package saleMarket.dao;

import saleMarket.domain.Order;
import saleMarket.service.CustomerService;
import saleMarket.service.FreightCargoService;
import saleMarket.service.ShopOwnerService;
import util.JdbcHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;

public final class OrderDao {
    private static OrderDao orderDao = new OrderDao();

    private OrderDao() {
    }

    public static OrderDao getInstance() {
        return orderDao;
    }

    public static Collection<Order> findAll (Connection connection)
            throws SQLException {
        //创建集合类对象，用来保存所有记录代表的对象
        Collection<Order> order = new ArrayList<>();
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM `Order` ");
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        //id,orderStatus,shopOwner_id,customer_id,freightCargo_id
        while (resultSet.next()) {
            order.add(new Order(resultSet.getInt("id"),
                    resultSet.getString("orderStatus"),
                    ShopOwnerService.getInstance().find(resultSet.getInt("shopOwner_id")),
                    CustomerService.getInstance().find(resultSet.getInt("customer_id")),
                    FreightCargoService.getInstance().find(resultSet.getInt("freightCargo_id"))
            ));
        }
        JdbcHelper.close(resultSet, pstmt);
        return order;
    }

    public static Order find(int id, Connection connection) throws SQLException {
        Order order = null;
        //创建sql语句
        String find_sql = "select * from `order` where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            order = new Order(resultSet.getInt("id"),
                    resultSet.getString("orderStatus"),
                    ShopOwnerService.getInstance().find(resultSet.getInt("shopOwner_id")),
                    CustomerService.getInstance().find(resultSet.getInt("customer_id")),
                    FreightCargoService.getInstance().find(resultSet.getInt("freightCargo_id"))
            );
        }
        JdbcHelper.close(resultSet, pstmt);
        return order;
    }

    public static boolean update(Order order,
                                 Connection connection) throws SQLException {
        //创建sql语句
        //id,orderStatus_id,shopOwner_id,customer_id,freightCargo_id
        String updateGPC_sql = "update `Order` set " +
                "orderStatus = ?,shopOwner_id = ?,customer_id = ?,freightCargo_id = ? " +
                "where id = ?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateGPC_sql);
        //为预编译参数赋值
        pstmt.setInt(5, order.getId());
        pstmt.setString(1, order.getOrderStatus());
        pstmt.setInt(2, order.getShopOwner().getId());
        pstmt.setInt(3, order.getCustomer().getId());
        pstmt.setInt(4, order.getFreightCargo().getId());
        //获取执行结果
        int affectedRow = pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(null,pstmt);
        //返回执行结果
        return affectedRow>0;
    }

    public static boolean add(Order order, Connection connection) throws SQLException {
        //创建sql语句
        String addGPC_sql = "insert into `Order` (orderStatus,shopOwner_id,customer_id,freightCargo_id) " +
                        "values (?,?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGPC_sql);
        //为预编译参数赋值
        pstmt.setString(1, order.getOrderStatus());
        pstmt.setInt(2, order.getShopOwner().getId());
        pstmt.setInt(3, order.getCustomer().getId());
        pstmt.setInt(4, order.getFreightCargo().getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null, pstmt);
        return affectedRowNum > 0 ;
    }

    public static boolean delete(int id, Connection connection) throws SQLException {
        //创建sql语句
        String deleteGPC_sql = "DELETE FROM `Order` WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGPC_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //获取执行结果
        int affectedRow = pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(null,pstmt);
        //返回执行结果
        return affectedRow>0;
    }
}

